import { BusinessException } from '../../types/exceptions.js';
import { Pool as MySQLPool, ResultSetHeader } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysMenu,
  CreateSysMenuDto,
  UpdateSysMenuDto,
  SysMenuQueryDto,
} from '../../entities/system/SysMenu.js';
import { BaseService } from '../BaseService.js';

// SysMenu特定的数据库操作接口
interface SysMenuOperations extends DatabaseOperations<SysMenu> {
  getByParentId(parentId: number): Promise<SysMenu[]>;
  getByPermission(permission: string): Promise<SysMenu | null>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysMenu> implements SysMenuOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(menu: CreateSysMenuDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_menu (
                      name, type, parent_id, path, component, permission,
                      is_frame, is_cache, full_page, tag_bread, visible,
                      status, icon, remark, \`order\`, time, update_time
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      menu.name as string,
      menu.type as number,
      menu.parent_id as number,
      menu.path as string,
      menu.component as string,
      menu.permission as string,
      menu.is_frame as number,
      menu.is_cache as number,
      menu.full_page as number,
      menu.tag_bread as number,
      menu.visible as number,
      menu.status as number,
      menu.icon as string,
      menu.remark as string,
      menu.order as number,
      now,
      now,
    ];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).insertId;
  }

  async update(id: number, menu: UpdateSysMenuDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];

    Object.entries(menu).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        if (key === 'order') {
          updateFields.push('`order` = ?');
        } else {
          updateFields.push(`${key} = ?`);
        }
        params.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push('update_time = ?');
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_menu SET ${updateFields.join(', ')} WHERE id = ? AND disabled = 0`;
    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_menu SET disabled = 1, update_time = ? WHERE id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [Date.now(), id]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async getByParentId(parentId: number): Promise<SysMenu[]> {
    const sql = 'SELECT * FROM sys_menu WHERE parent_id = ? AND disabled = 0 ORDER BY `order` ASC';
    const [rows] = await this.pool.execute(sql, [parentId]);
    return rows as SysMenu[];
  }

  async getByPermission(permission: string): Promise<SysMenu | null> {
    const sql = 'SELECT * FROM sys_menu WHERE permission = ? AND disabled = 0 LIMIT 1';
    const [rows] = await this.pool.execute(sql, [permission]);
    const result = rows as SysMenu[];
    return result.length > 0 ? result[0] : null;
  }

  async getById(id: number): Promise<SysMenu | null> {
    const sql = 'SELECT * FROM sys_menu WHERE id = ? AND disabled = 0 LIMIT 1';
    const [rows] = await this.pool.execute(sql, [id]);
    const result = rows as SysMenu[];
    return result.length > 0 ? result[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysMenuQueryDto = {}
  ): Promise<{
    list: SysMenu[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];

    if (conditions.name) {
      whereConditions.push('name LIKE ?');
      params.push(`%${conditions.name}%`);
    }
    if (conditions.type !== undefined) {
      whereConditions.push('type = ?');
      params.push(conditions.type);
    }
    if (conditions.parent_id !== undefined) {
      whereConditions.push('parent_id = ?');
      params.push(conditions.parent_id);
    }
    if (conditions.status !== undefined) {
      whereConditions.push('status = ?');
      params.push(conditions.status);
    }
    if (conditions.visible !== undefined) {
      whereConditions.push('visible = ?');
      params.push(conditions.visible);
    }

    const whereClause = whereConditions.join(' AND ');

    // 获取总数
    const countSql = `SELECT COUNT(*) as total FROM sys_menu WHERE ${whereClause}`;
    const [countResult] = await this.pool.execute(countSql, params);
    const total = (countResult as Array<{ total: number }>)[0].total;

    // 获取分页数据
    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_menu WHERE ${whereClause} ORDER BY \`order\` ASC, id ASC LIMIT ? OFFSET ?`;
    const [rows] = await this.pool.execute(listSql, [...params, pageSize, offset]);

    return {
      list: rows as SysMenu[],
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations extends BasePostgreSQLOperations<SysMenu> implements SysMenuOperations {
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(menu: CreateSysMenuDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_menu (
                      name, type, parent_id, path, component, permission,
                      is_frame, is_cache, full_page, tag_bread, visible,
                      status, icon, remark, "order", time, update_time
                  ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
                  RETURNING id`;
    const params: (string | number)[] = [
      menu.name as string,
      menu.type as number,
      menu.parent_id as number,
      menu.path as string,
      menu.component as string,
      menu.permission as string,
      menu.is_frame as number,
      menu.is_cache as number,
      menu.full_page as number,
      menu.tag_bread as number,
      menu.visible as number,
      menu.status as number,
      menu.icon as string,
      menu.remark as string,
      menu.order as number,
      now,
      now,
    ];

    const result = await this.pool.query(sql, params);
    return result.rows[0].id;
  }

  async update(id: number, menu: UpdateSysMenuDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(menu).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        if (key === 'order') {
          updateFields.push(`"order" = $${paramIndex}`);
        } else {
          updateFields.push(`${key} = $${paramIndex}`);
        }
        params.push(value as string | number);
        paramIndex++;
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push(`update_time = $${paramIndex}`);
    params.push(Date.now());
    paramIndex++;

    params.push(id);
    const sql = `UPDATE sys_menu SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = 0`;
    const result = await this.pool.query(sql, params);
    return (result.rowCount || 0) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_menu SET disabled = 1, update_time = $1 WHERE id = $2 AND disabled = 0';
    const result = await this.pool.query(sql, [Date.now(), id]);
    return (result.rowCount || 0) > 0;
  }

  async getByParentId(parentId: number): Promise<SysMenu[]> {
    const sql = 'SELECT * FROM sys_menu WHERE parent_id = $1 AND disabled = 0 ORDER BY "order" ASC';
    const result = await this.pool.query(sql, [parentId]);
    return result.rows as SysMenu[];
  }

  async getByPermission(permission: string): Promise<SysMenu | null> {
    const sql = 'SELECT * FROM sys_menu WHERE permission = $1 AND disabled = 0 LIMIT 1';
    const result = await this.pool.query(sql, [permission]);
    return result.rows.length > 0 ? (result.rows[0] as SysMenu) : null;
  }

  async getById(id: number): Promise<SysMenu | null> {
    const sql = 'SELECT * FROM sys_menu WHERE id = $1 AND disabled = 0 LIMIT 1';
    const result = await this.pool.query(sql, [id]);
    return result.rows.length > 0 ? (result.rows[0] as SysMenu) : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysMenuQueryDto = {}
  ): Promise<{
    list: SysMenu[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions.name) {
      whereConditions.push(`name ILIKE $${paramIndex}`);
      params.push(`%${conditions.name}%`);
      paramIndex++;
    }
    if (conditions.type !== undefined) {
      whereConditions.push(`type = $${paramIndex}`);
      params.push(conditions.type);
      paramIndex++;
    }
    if (conditions.parent_id !== undefined) {
      whereConditions.push(`parent_id = $${paramIndex}`);
      params.push(conditions.parent_id);
      paramIndex++;
    }
    if (conditions.status !== undefined) {
      whereConditions.push(`status = $${paramIndex}`);
      params.push(conditions.status);
      paramIndex++;
    }
    if (conditions.visible !== undefined) {
      whereConditions.push(`visible = $${paramIndex}`);
      params.push(conditions.visible);
      paramIndex++;
    }

    const whereClause = whereConditions.join(' AND ');

    // 获取总数
    const countSql = `SELECT COUNT(*) as total FROM sys_menu WHERE ${whereClause}`;
    const countResult = await this.pool.query(countSql, params);
    const total = parseInt(countResult.rows[0].total);

    // 获取分页数据
    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_menu WHERE ${whereClause} ORDER BY "order" ASC, id ASC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const listResult = await this.pool.query(listSql, [...params, pageSize, offset]);

    return {
      list: listResult.rows as SysMenu[],
      total,
      page,
      pageSize,
    };
  }
}

export class SysMenuService extends BaseService<
  SysMenu,
  CreateSysMenuDto,
  UpdateSysMenuDto,
  SysMenuQueryDto,
  SysMenuOperations
> {
  constructor() {
    super(MySQLOperations, PostgreSQLOperations);
  }

  /**
   * 删除菜单（重写以添加子菜单检查）
   * @param id 菜单ID
   * @returns 是否删除成功
   */
  async delete(id: number): Promise<boolean> {
    // 检查是否有子菜单
    const children = await this.getDbOperations().getByParentId(id);
    if (children.length > 0) {
      throw new BusinessException('存在子菜单，无法删除');
    }
    return super.delete(id);
  }

  /**
   * 根据父菜单ID获取子菜单
   * @param parentId 父菜单ID
   * @returns 子菜单列表
   */
  async getByParentId(parentId: number): Promise<SysMenu[]> {
    return this.getDbOperations().getByParentId(parentId);
  }

  /**
   * 根据权限标识获取菜单
   * @param permission 权限标识
   * @returns 菜单信息或null
   */
  async getByPermission(permission: string): Promise<SysMenu | null> {
    return this.getDbOperations().getByPermission(permission);
  }
}
